using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public static partial class MayorService
    {
        public static bool AddMayor(Mayor mayor)
        {
            string sql =
                "INSERT Mayor (Title, Count, VideoContent, Image, Region, UploadDate, XUHAO)" +
                "VALUES (@Title, @Count, @VideoContent, @Image, @Region, @UploadDate, @XUHAO)";

            sql += " ; SELECT @@IDENTITY";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@Title", mayor.Title),
				new SqlParameter("@Count", mayor.Count),
				new SqlParameter("@VideoContent", mayor.VideoContent),
				new SqlParameter("@Image", mayor.Image),
				new SqlParameter("@Region", mayor.Region),
				new SqlParameter("@UploadDate", mayor.UploadDate),
				new SqlParameter("@XUHAO", mayor.XUHAO)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static int AddMayorReturnID(Mayor mayor)
        {
            string sql =
                "INSERT Mayor (Title, Count, VideoContent, Image, Region, UploadDate, XUHAO)" +
                "VALUES (@Title, @Count, @VideoContent, @Image, @Region, @UploadDate, @XUHAO)";

            sql += " ; SELECT @@IDENTITY";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@Title", mayor.Title),
				new SqlParameter("@Count", mayor.Count),
				new SqlParameter("@VideoContent", mayor.VideoContent),
				new SqlParameter("@Image", mayor.Image),
				new SqlParameter("@Region", mayor.Region),
				new SqlParameter("@UploadDate", mayor.UploadDate),
				new SqlParameter("@XUHAO", mayor.XUHAO)
			};

            int i = Convert.ToInt32(DBHelper.ExecuteScalar(CommandType.Text, sql, para));
            return i;
        }


        public static bool DeleteMayorByID(int iD)
        {
            string sql = "DELETE Mayor WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static bool ModifyMayor(Mayor mayor)
        {
            string sql =
                "UPDATE Mayor " +
                "SET " +
                    "Title = @Title, " +
                    "Count = @Count, " +
                    "VideoContent = @VideoContent, " +
                    "Image = @Image, " +
                    "Region = @Region, " +
                    "UploadDate = @UploadDate, " +
                    "XUHAO = @XUHAO " +
                "WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", mayor.ID),
				new SqlParameter("@Title", mayor.Title),
				new SqlParameter("@Count", mayor.Count),
				new SqlParameter("@VideoContent", mayor.VideoContent),
				new SqlParameter("@Image", mayor.Image),
				new SqlParameter("@Region", mayor.Region),
				new SqlParameter("@UploadDate", mayor.UploadDate),
				new SqlParameter("@XUHAO", mayor.XUHAO)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static bool ModifyMayorInfoCount(int id, int count)
        {
            string sql = "UPDATE Mayor SET Count = @Count WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", id),
				new SqlParameter("@Count", count)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static IList<Mayor> GetAllMayors()
        {
            string sqlAll = "SELECT * FROM Mayor order by XUHAO desc,UploadDate desc";
            return GetMayorsBySql(sqlAll);
        }

        public static IList<Mayor> GetMayorsByTop10()
        {
            string sqlAll = "SELECT top 10 * FROM Mayor order by XUHAO desc";
            return GetMayorsBySql(sqlAll);
        }

        public static IList<Mayor> GetMayorsByTitle(string title)
        {
            string sqlAll = "SELECT * FROM Mayor where Title like '%" + title + "%'";
            return GetMayorsBySql(sqlAll);
        }

        public static Mayor GetMayorByID(int iD)
        {
            string sql = "SELECT * FROM Mayor WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            Mayor mayor = new Mayor();

            foreach (DataRow dr in temp.Rows)
            {
                mayor.ID = (int)dr["ID"];
                mayor.Title = (string)dr["Title"];
                mayor.Count = (int)dr["Count"];
                mayor.VideoContent = (string)dr["VideoContent"];
                mayor.Image = (string)dr["Image"];
                mayor.Region = (string)dr["Region"];
                mayor.UploadDate = (DateTime)dr["UploadDate"];
                mayor.XUHAO = (int)dr["XUHAO"];

            }
            return mayor;

        }


        private static IList<Mayor> GetMayorsBySql(string safeSql)
        {
            List<Mayor> list = new List<Mayor>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, null))
            {
                while (dr.Read())
                {
                    Mayor mayor = new Mayor();

                    mayor.ID = (int)dr["ID"];
                    mayor.Title = (string)dr["Title"];
                    mayor.Count = (int)dr["Count"];
                    mayor.VideoContent = (string)dr["VideoContent"];
                    mayor.Image = (string)dr["Image"];
                    mayor.Region = (string)dr["Region"];
                    mayor.UploadDate = (DateTime)dr["UploadDate"];
                    mayor.XUHAO = (int)dr["XUHAO"];

                    list.Add(mayor);
                }
            }
            return list;
        }

        private static IList<Mayor> GetMayorsBySql(string safeSql, params SqlParameter[] para)
        {
            List<Mayor> list = new List<Mayor>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, para))
            {
                while (dr.Read())
                {
                    Mayor mayor = new Mayor();

                    mayor.ID = (int)dr["ID"];
                    mayor.Title = (string)dr["Title"];
                    mayor.Count = (int)dr["Count"];
                    mayor.VideoContent = (string)dr["VideoContent"];
                    mayor.Image = (string)dr["Image"];
                    mayor.Region = (string)dr["Region"];
                    mayor.UploadDate = (DateTime)dr["UploadDate"];
                    mayor.XUHAO = (int)dr["XUHAO"];

                    list.Add(mayor);
                }
            }
            return list;
        }
    }
}
